﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mads.CoreDomain;
using Mads.CoreDomain.Enumerations;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Reflection;
using System.IO;

namespace Mads.WcfService
{
  //TODO:: add data about users who made changes
  internal sealed partial class TrainBaseConnector : IDisposable
  {
    #region private fields
    private Dictionary<Type, string> tableNames = new Dictionary<Type, string>
    {
      { typeof(Car), "Car" },
      { typeof(CarType), "CarType" },
      { typeof(Order), "Order" },
      { typeof(Race), "Race" },
      { typeof(Route), "Route" },
      { typeof(RouteStop), "RouteToStation" },
      { typeof(SeasonPrice), "SeasonPrice" },
      { typeof(Seat), "Seat" }, //because I'm stupid
      { typeof(Station), "Station" },
      { typeof(Ticket), "Ticket" },
      { typeof(TicketOwner), "TicketOwner" },
      { typeof(TicketPrice), "StationsPrice" },
      { typeof(User), "User" }
    };

    private delegate bool AddMethod(uint id, KeyAbstraction item);
    private delegate KeyAbstraction GetterMethod(MySqlDataReader reader);

    private Dictionary<Type, AddMethod> addMethods
      = new Dictionary<Type, AddMethod>();

    private Dictionary<Type, GetterMethod> getters
      = new Dictionary<Type, GetterMethod>();

    private Dictionary<uint, CarType> carTypesCache
      = new Dictionary<uint, CarType>();



    //private MySqlConnection connection = new MySqlConnection(connectionString);
    private MySqlConnection connection;

    private static TrainBaseConnector instance;
    #endregion

    #region private methods
    private TrainBaseConnector()
    {
      string conString = GetConnectionString();
      connection = new MySqlConnection(conString);
      connection.Open();

      LoadMetadata();
      InitDictionaries();
      //Add<User>(new User("admin", "admin", "",UserRole.Administrator));
    }

    private string GetConnectionString()
    {
      string connString;
      string location = Path.Combine(
        Assembly.GetExecutingAssembly().Location,
        @"..\config.conf");
      using (StreamReader sr = File.OpenText(location))
      {
        connString = sr.ReadToEnd();
      }
      connString += "database = mads; charset = utf8;";
      return connString;
    }


    private bool ContainsUser(string login, out string password, out UserRole role)
    {
      password = "";
      role = UserRole.User;
      using (var command = new MySqlCommand(getUsers, connection))
      {
        command.Parameters.AddWithValue("@login", login);
        MySqlDataReader reader = command.ExecuteReader();
        bool contains = false;
        if (reader.Read())
        {
          password = reader.IsDBNull(2) ? "" : reader.GetString(2);
          role = (UserRole)(reader.IsDBNull(3) ? 0 : reader.GetInt32(3));
          contains = true;
        }
        reader.Close();
        return contains;
      }
    }

    private uint NextID(string query)
    {
      using (var command = new MySqlCommand(query, connection))
      {
        MySqlDataReader reader = command.ExecuteReader();
        int next = 1;
        if (reader.Read())
        {
          next = reader.IsDBNull(0) ? 1 : reader.GetInt32(0);
        }
        reader.Close();
        return (uint)next;
      }
    }

    private static string ToSqlString(DateTime datetime)
    {
      return datetime.ToString("yy-MM-dd HH:mm:ss");
    }

    private static string GetSelectByID(string tableName, uint id)
    {
      return String.Format(
        @"SELECT * FROM {0} WHERE ID = {1}",
        tableName, id);
    }

    private static string GetDeleteByID(string tableName, uint id)
    {
      return string.Format(
        @"DELETE FROM {0} WHERE ID = {1}",
        tableName, id);
    }

    /// <summary>
    /// Loads rarely updated data from database to store it in cache.
    /// </summary>
    private void LoadMetadata()
    {
      const string query = "SELECT * FROM CarType";

      using (var command = new MySqlCommand(query, connection))
      {
        MySqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
          uint id = (uint)reader.GetInt32("ID");
          carTypesCache.Add(
            id,
            new CarType(
              id,
              reader.GetString("Name"),
              reader.GetFloat("PriceCoefficient"),
              (uint)reader.GetInt32("SitsNumber")));
        }
        reader.Close();
      }
    }

    private void InitDictionaries()
    {
      addMethods[typeof(CarType)] = AddCarType;
      addMethods[typeof(Race)] = AddRace;
      addMethods[typeof(SeasonPrice)] = AddSeasonPrice;
      addMethods[typeof(Ticket)] = AddTicket;
      addMethods[typeof(TicketOwner)] = AddTicketOwner;
      addMethods[typeof(User)] = AddUser;

      getters[typeof(Ticket)] = GetTicket;
    }

    private bool AddCarType(uint id, KeyAbstraction item)
    {
      CarType type = item as CarType;
      using (var command = new MySqlCommand(addCarType, connection))
      {
        command.Parameters.AddWithValue("@name", type.Name);
        command.Parameters.AddWithValue("@priceCoefficient", type.PriceMultiplier);
        command.Parameters.AddWithValue("@sitsNumber", type.SeatsNumber);
        command.ExecuteNonQuery();
      }
      return true;
    }

    private bool AddRace(uint id, KeyAbstraction item)
    {
      Race race = item as Race;
      if (race.Route.ID == null)
      {
        return false;
      }
      using (var command = new MySqlCommand(addRace, connection))
      {
        command.Parameters.AddWithValue("@departure", race.Departure);
        command.Parameters.AddWithValue("@routeID", race.Route.ID);
        command.ExecuteNonQuery();
      }
      return true;
    }

    private bool AddSeasonPrice(uint id, KeyAbstraction item)
    {
      SeasonPrice price = item as SeasonPrice;
      using (var command = new MySqlCommand(addSeasonPrice, connection))
      {
        command.Parameters.AddWithValue("@priceCoefficient", price.PriceMultiplier);
        command.Parameters.AddWithValue("@startDate", price.StartDate);
        command.Parameters.AddWithValue("@endDate", price.EndDate);
        command.Parameters.AddWithValue("@version", 1);
        command.ExecuteNonQuery();
      }
      return true;
    }

    private bool AddTicket(uint id, KeyAbstraction item)
    {
      Ticket ticket = item as Ticket;

      MySqlCommand command = new MySqlCommand(addTicket, connection);
      //since addTicket is there, all parameters are required
      if (ticket.Owner != null)
      {
        Add<TicketOwner>(ticket.Owner);
        if (ticket.Owner.ID == null)
        {
          return false;
        }
        command.Parameters.AddWithValue("@ownerID", ticket.Owner.ID.Value);
      }
      else
      {
        command.Parameters.AddWithValue("@ownerID", null);
      }

      command.Parameters.AddWithValue("@buyerID", ticket.Buyer);
      command.Parameters.AddWithValue("@state", (int)ticket.State);
      command.Parameters.AddWithValue("@startStationID", ticket.StartStation.ID);
      command.Parameters.AddWithValue("@endStationID", ticket.EndStation.ID);
      command.Parameters.AddWithValue("@sitID", ticket.Seat.ID);
      command.ExecuteNonQuery();
      command.Dispose();
      return true;
    }

    private bool AddTicketOwner(uint id, KeyAbstraction item)
    {
      TicketOwner owner = item as TicketOwner;

      using (var command = new MySqlCommand(addTicketOwner, connection))
      {
        command.Parameters.AddWithValue("@ID", id);
        command.Parameters.AddWithValue("@name", owner.Name);
        command.Parameters.AddWithValue("@docNumber", owner.DocNumber);
        command.ExecuteNonQuery();
      }
      return true;
    }

    private bool AddUser(uint id, KeyAbstraction item)
    {
      User user = item as User;
      string pass;
      UserRole role;
      if (ContainsUser(user.Login, out pass, out role))
      {
        return false;
      }

      using (var command = new MySqlCommand(addUser, connection))
      {
        command.Parameters.AddWithValue("@login", user.Login);
        command.Parameters.AddWithValue("@password", user.PasswordHash);
        command.Parameters.AddWithValue("@role", (int)user.Role);
        command.ExecuteNonQuery();
      }
      return true;
    }

    private KeyAbstraction GetTicket(MySqlDataReader reader)
    {
      uint? ownerID = (uint?)reader.GetNullable(reader.GetInt32, "OwnerID");

      return new Ticket(
          new Seat((uint)reader.GetInt32("SitID")),
          new RouteStop((uint)reader.GetInt32("StartStationID")),
          new RouteStop((uint)reader.GetInt32("EndStationID")),
          (TicketState)(reader.GetInt16("State")))
      {
        ID = (uint)reader.GetInt32("ID"),
        Owner = ownerID == null ? null : new TicketOwner(ownerID.Value),
        LogicalID = reader.GetStringOrNull("LogicalID"),
        BookedUntil = reader.GetNullable<DateTime>(reader.GetDateTime, "BookedUntil"),
        Bought = reader.GetNullable<DateTime>(reader.GetDateTime, "Bought")
      };
    }

    private static void FillVersionedKeyAbstraction(
      MySqlDataReader reader, VersionedKeyAbstraction result)
    {
      try
      {
        result.ID = (uint)reader.GetInt32("ID");
        result.CreatedAt = reader.GetDateTime("CreatedAt");
        result.ModifiedAt = reader.GetDateTime("ModifiedAt");
        result.Version = (uint)reader.GetInt32("Version");
        result.Obsolete = reader.GetBoolean("Obsolete");
      }
      // the worst way to ignore NULLS in cells
      catch (Exception) { }
    }

    /// <summary>
    /// To be used when data is read from vwTicket.
    /// </summary>
    private Ticket FillTicketWitData(MySqlDataReader reader)
    {
      Route route = new Route((uint)reader.GetInt32("RouteID"));

      Car car = new Car(
        (uint)reader.GetInt32("CarNumber"),
        route,
        carTypesCache[(uint)reader.GetInt32("CarTypeID")]
        ) { ID = (uint)reader.GetInt32("CarID") };

      Seat seat = new Seat(
        (uint)reader.GetInt32("SeatNumber"),
        car,
        new Race((uint)reader.GetInt32("RaceID")) { Route = route }
        ) { ID = (uint)reader.GetInt32("SitID") };

      uint? ownerID = (uint?)reader.GetNullable(reader.GetInt32, "OwnerID");

      Ticket result = new Ticket(
        seat,
        new RouteStop((uint)reader.GetInt32("StartStationID")),
        new RouteStop((uint)reader.GetInt32("EndStationID")),
        (TicketState)(reader.GetInt16("State")))
      {
        ID = (uint)reader.GetInt32("ID"),
        Owner = ownerID == null
          ? null
          : new TicketOwner(ownerID.Value)
            {
              Name = reader.GetStringOrNull("Name"),
              DocNumber = reader.GetStringOrNull("DocNumber")
            },
        LogicalID = reader.GetStringOrNull("LogicalID"),
        BookedUntil = reader.GetNullable<DateTime>(reader.GetDateTime, "BookedUntil"),
        Bought = reader.GetNullable<DateTime>(reader.GetDateTime, "Bought")
      };
      return result;
    }
    #endregion

    #region public methods
    public static TrainBaseConnector Instance
    {
      get { return instance ?? (instance = new TrainBaseConnector()); }
    }

    public void Dispose()
    {
      connection.Close();
    }
    /// <summary>
    /// Gets key abstraction of required type by its system id.
    /// </summary>
    public T Get<T>(uint id) where T : KeyAbstraction
    {
      Type type = typeof(T);
      string query = GetSelectByID(tableNames[type], id);

      using (var command = new MySqlCommand(query, connection))
      {
        MySqlDataReader reader = command.ExecuteReader();
        T result = reader.Read() ? (T)getters[type](reader) : null;
        reader.Close();
        return result;
      }
    }

    public void Delete<T>(uint id) where T : KeyAbstraction
    {
      Type type = typeof(T);
      string query = GetDeleteByID(tableNames[type], id);

      //TODO - send query
    }

    // forms tickets that doesn't exist, but could be bought
    public IEnumerable<FreeTicket> GetTickets(
      DateTime startTime, DateTime endTime,
      Station departureStation, Station arrivalStation)
    {
      //HACK: to avoid crash we allow arguments to be null
      if (departureStation == null || arrivalStation == null)
      {
        return Enumerable.Empty<FreeTicket>();
      }
      string query = String.Format(
        findTicketsTemplate,
        departureStation.ID,
        arrivalStation.ID,
        (int)TicketState.Free,
        ToSqlString(startTime),
        ToSqlString(endTime));

      using (MySqlCommand command = new MySqlCommand(query, connection))
      {
        MySqlDataReader reader = command.ExecuteReader();
        List<FreeTicket> tickets = new List<FreeTicket>();
        while (reader.Read())
        {
          tickets.Add(new FreeTicket(
            (uint)reader.GetInt32("ID"),
            (uint)reader.GetInt32("RouteNumber"),
            reader.GetDateTime("RealDeparture"),
            reader.GetDateTime("RealArrival"),
            carTypesCache[(uint)reader.GetInt32("CarTypeID")],
            0, //price will be calculated later
            (uint)reader.GetInt32("CarNumber"),
            (uint)reader.GetInt32("SeatNumber")));
        }
        reader.Close();
        return tickets;
      }
    }

    public TicketPrice GetTicketPrice(Station departureStation, Station arrivalStation)
    {
      //HACK: to avoid crash we allow arguments to be null
      if (departureStation == null || arrivalStation == null)
      {
        return new TicketPrice(null, null, 1);
      }

      using (var command = new MySqlCommand(getTicketPrice, connection))
      {
        command.Parameters.AddWithValue("@startStopID", departureStation.ID);
        command.Parameters.AddWithValue("@endStopID", arrivalStation.ID);
        MySqlDataReader reader = command.ExecuteReader();
        if (!reader.Read())
        {
          reader.Close();
          return null;
        }
        var result = new TicketPrice(
          departureStation, arrivalStation, reader.GetFloat("Price"));
        FillVersionedKeyAbstraction(reader, result);
        reader.Close();
        return result;
      }
    }

    public SeasonPrice GetSeasonPrice(DateTime date)
    {
      using (var command = new MySqlCommand(getSeasonPrice, connection))
      {
        command.Parameters.AddWithValue("@date", date);
        MySqlDataReader reader = command.ExecuteReader();
        if (!reader.Read())
        {
          reader.Close();
          //return null;
          //HACK - if there is no price in base return 1
          return new SeasonPrice(date, date, 1);
        }
        SeasonPrice result = new SeasonPrice(
          reader.GetDateTime("StartDate"),
          reader.GetDateTime("EndDate"),
          reader.GetFloat("PriceCoefficient"));
        FillVersionedKeyAbstraction(reader, result);
        reader.Close();
        return result;
      }
    }

    /// <summary>
    /// Get route stop for given route and station.
    /// </summary>
    public RouteStop GetRouteStop(Route route, Station station)
    {
      using (var command =
        new MySqlCommand(getRouteStopByRouteAndStation, connection))
      {
        command.Parameters.AddWithValue("@routeID", route.ID.Value);
        command.Parameters.AddWithValue("@stationID", station.ID.Value);
        MySqlDataReader reader = command.ExecuteReader();
        RouteStop result = reader.Read()
         ? new RouteStop(
          route,
          station,
          (uint)reader.GetInt32("StationNumber"),
          (uint)reader.GetNullable<int>(reader.GetInt32, "Departure"),
          (uint)reader.GetNullable<int>(reader.GetInt32, "Arrival")
         ) { ID = (uint)reader.GetInt32("ID") }
          : null;
        reader.Close();
        return result;
      }
    }

    /// <summary>
    /// Change ticket in base according to given info.
    /// </summary>
    public bool Persist(Ticket ticket)
    {
      using (var command = new MySqlCommand(buyTicketTemplate, connection))
      {
        command.Parameters.AddWithValue("@id", ticket.ID);
        command.Parameters.AddWithValue("@state", ticket.State);
        command.Parameters.AddWithValue("@startStopID", ticket.StartStation.ID);
        command.Parameters.AddWithValue("@endStopID", ticket.EndStation.ID);
        command.Parameters.AddWithValue("@ownerID", ticket.Owner == null ? null : ticket.Owner.ID);
        command.Parameters.AddWithValue("@buyerID", ticket.Buyer == null ? null : ticket.Buyer.ID);
        command.Parameters.AddWithValue("@bought", ticket.Bought);
        command.Parameters.AddWithValue("@logicalID", ticket.LogicalID);
        command.ExecuteNonQuery();
      }
      return true;
    }

    /// <summary>
    /// Find free tickets adjacent to given one
    /// (to eliminate them when it is returned).
    /// </summary>
    public IEnumerable<Ticket> GetAdjacentFreeTickets(Ticket ticket)
    {
      using (var command = new MySqlCommand(getAdjacentFreeTickets, connection))
      {
        command.Parameters.AddWithValue("@seatID", ticket.Seat.ID);
        command.Parameters.AddWithValue("@state", TicketState.Free);
        command.Parameters.AddWithValue("@startStopID", ticket.StartStation.ID);
        command.Parameters.AddWithValue("@endStopID", ticket.EndStation.ID);
        var reader = command.ExecuteReader();
        while (reader.Read())
        {
          yield return new Ticket(
            ticket.Seat,
            new RouteStop((uint)reader.GetInt32("StartStationID")),
            new RouteStop((uint)reader.GetInt32("EndStationID")),
            TicketState.Free
            ) { ID = (uint)reader.GetInt32("ID") };
        }
        reader.Close();
      }
    }

    /// <summary>
    /// Get ticket with all information by its id.
    /// </summary>
    public Ticket GetTicketWithInfo(uint id)
    {
      Ticket result;
      using (var command = new MySqlCommand(getTicketWithInfo, connection))
      {
        command.Parameters.AddWithValue("@id", id);
        MySqlDataReader reader = command.ExecuteReader();
        result = reader.Read()
          ? FillTicketWitData(reader)
          : null;
        reader.Close();
      }
        return result;
      
    }

    public Ticket GetTicketByNumber(string logicalID)
    {
      Ticket result;
      using (var command = new MySqlCommand(getTicketByNumber, connection))
      {
        command.Parameters.AddWithValue("@logicalID", logicalID);
        MySqlDataReader reader = command.ExecuteReader();
        if (!reader.Read())
        {
          reader.Close();
          return null;
        }
        result = FillTicketWitData(reader);
      
        reader.Close();
      }
      
      MySqlCommand readRoute = new MySqlCommand(
          GetSelectByID("route", (uint)result.Seat.Race.Route.ID), connection);
      MySqlDataReader read = readRoute.ExecuteReader();
      while (read.Read())
      {
        result.Seat.Race.Route.LogicalID = (uint)read.GetInt16("RouteNumber");
      }
      read.Close();
      readRoute.Dispose();
      return result;  
    }    

    public void Add<T>(T item) where T : KeyAbstraction
    {
      Type type = typeof(T);
      uint id = NextID(String.Format(getNextIDTemplate, tableNames[type]));
      item.ID =
        addMethods[typeof(T)](id, item)
        ? (uint?)id
        : null;
    }

    public bool DeleteRoute(Route route)
    {
      return false;
    }

    public bool ChangeRoute(Route previousRout, Route newRout)
    {
      return false;
    }

    public bool ChangeStationState(string name, bool state)
    {
      return false;
    }

    public User Login(string name, string password)
    {
      string pass = "";
      UserRole role = UserRole.User;
      if (ContainsUser(name, out pass, out role) && pass == password)
      {
        return new User(name, pass, "", role);
      }
      return null;
    }

    public DateTime GetLastTicketCreation()
    {
      MySqlCommand command = new MySqlCommand(getLastDeparture, connection);
      MySqlDataReader reader = command.ExecuteReader();
      DateTime lastCreation = DateTime.Today.AddDays(-1);
      while (reader.Read())
      {
        if (reader.IsDBNull(0))
        {
          reader.Close();
          command.Dispose();
          return lastCreation;
        }
        lastCreation = reader.GetDateTime(0).AddDays(-40);
      }
      reader.Close();
      command.Dispose();
      return lastCreation;
    }
    public List<Station> GetStations()
    {
      List<Station> stations = new List<Station>();
      MySqlCommand command = new MySqlCommand(selectActualStations, connection);
      MySqlDataReader reader = command.ExecuteReader();
      while (reader.Read())
      {
        uint id = (uint)reader.GetInt32(0);
        string name = reader.GetString(1);
        stations.Add(new Station(id, name));
      }
      reader.Close();
      command.Dispose();
      return stations;
    }
    public void AddRaces(DateTime date)
    {
      MySqlCommand command = new MySqlCommand(selectRoutesAndDeparture, connection);
      command.Parameters.AddWithValue("@reg", 2 - date.DayOfYear % 2);
      MySqlDataReader reader = command.ExecuteReader();
      List<Race> races = new List<Race>();
      while (reader.Read())
      {
        uint id = (uint)reader.GetInt32(1);
        int departure = reader.GetInt16(0);
        DateTime finalDate = date + new TimeSpan(0, departure, 0);
        races.Add(new Race(new Route(id), finalDate));
      }
      reader.Close();
      command.Dispose();
      foreach (Race race in races)
      {
        MySqlCommand insert = new MySqlCommand(addRace, connection);
        insert.Parameters.AddWithValue("@departure", race.Departure);
        insert.Parameters.AddWithValue("@routeID", race.Route.ID);
        insert.ExecuteNonQuery();
        insert.Dispose();
        Console.WriteLine("race was added");
      }
    }
    public void AddSeats(DateTime date)
    {
      MySqlCommand command = new MySqlCommand(getSeatsInformation, connection);
      string day = ToSqlString(date.Date);
      string nextDay = ToSqlString(date.Date + new TimeSpan(1, 0, 0, 0));
      command.Parameters.AddWithValue("@time1", day);
      command.Parameters.AddWithValue("@time2", nextDay);
      List<Seat> seats = new List<Seat>();
      MySqlDataReader reader = command.ExecuteReader();
      while (reader.Read())
      {
        uint carId = (uint)reader.GetInt32(0);
        uint raceId = (uint)reader.GetInt32(1);
        int seatsNumber = reader.GetInt32(2);
        for (uint i = 1; i <= seatsNumber; i++)
        {
          Seat seat = new Seat(i, new Car(carId), new Race(raceId));
          seats.Add(seat);
        }
      }
      reader.Close();
      command.Dispose();
      foreach (Seat seat in seats)
      {
        MySqlCommand insert = new MySqlCommand(addSit, connection);
        insert.Parameters.AddWithValue("@carID", seat.Car.ID);
        insert.Parameters.AddWithValue("@raceID", seat.Race.ID);
        insert.Parameters.AddWithValue("@seatsNumber", seat.Number);
        insert.ExecuteNonQuery();
        insert.Dispose();
        Console.WriteLine("seat was added");
      }
    }
    public void AddTickets(DateTime date)
    {
      MySqlCommand command = new MySqlCommand(getTicketInformation, connection);
      command.Parameters.AddWithValue("@time1", ToSqlString(date.Date));
      DateTime nextDay = date.Date.AddDays(1);
      command.Parameters.AddWithValue("@time2", ToSqlString(nextDay));
      MySqlDataReader reader = command.ExecuteReader();
      List<Ticket> tickets = new List<Ticket>();
      while (reader.Read())
      {
        uint startStation = (uint)reader.GetInt32(0);
        uint endStation = (uint)reader.GetInt32(1);
        uint seat = (uint)reader.GetInt32(2);
        Ticket ticket = new Ticket(new Seat(seat), new RouteStop(startStation),
          new RouteStop(endStation), TicketState.Free);
        tickets.Add(ticket);
      }
      reader.Close();
      command.Dispose();
      foreach (Ticket ticket in tickets)
      {
        MySqlCommand insert = new MySqlCommand(addFreeTicket, connection);
        insert.Parameters.AddWithValue("@startStationID", ticket.StartStation.ID);
        insert.Parameters.AddWithValue("@endStationID", ticket.EndStation.ID);
        insert.Parameters.AddWithValue("@sitID", ticket.Seat.ID);
        insert.ExecuteNonQuery();
        insert.Dispose();
        Console.WriteLine("ticket was added");
      }
    }

    public void ObsoleteSeasonPrice()
    {
      MySqlCommand command = new MySqlCommand(obsoleteSeasonPrice, connection);
      command.ExecuteNonQuery();
      command.Dispose();
    }
    //i don't want to make one more select to data base to get ID, 
    //functions add elements without unique ID
    //and application crashes if some table is empty
    public void AddRoute(Route route)
    {
      MySqlCommand command = new MySqlCommand(addRoute, connection);
      command.Parameters.AddWithValue("@routeNumber", route.LogicalID);
      command.Parameters.AddWithValue("@expressType", (int)route.SpeedType);
      command.Parameters.AddWithValue("@regularity", (int)route.Regularity);
      command.Parameters.AddWithValue("@priceCoefficient", route.PriceMultiplier);
      command.ExecuteNonQuery();
      command.Dispose();
    }
    public void AddCar(Car car)
    {
      MySqlCommand command = new MySqlCommand(addCar, connection);
      command.Parameters.AddWithValue("@number", car.Number);
      command.Parameters.AddWithValue("@carType", car.CarType.ID);
      command.Parameters.AddWithValue("@routeNumber", car.Route.LogicalID);
      command.ExecuteNonQuery();
      command.Dispose();
    }
    public void AddRouteStop(RouteStop stop)
    {
      MySqlCommand command = new MySqlCommand(addRouteToStation, connection);
      command.Parameters.AddWithValue("@arrival", stop.ArrivalTime);
      command.Parameters.AddWithValue("@departure", stop.DepartureTime);
      command.Parameters.AddWithValue("@stationNumber", stop.StationNumber);
      command.Parameters.AddWithValue("@stationID", stop.Station.ID);
      command.Parameters.AddWithValue("@routeNumber", stop.Route.LogicalID);
      command.ExecuteNonQuery();
      command.Dispose();
    }
    public List<CarType> GetCarTypes()
    {
      return carTypesCache.Values.ToList();
    }

    #region Новые метода для приложения для администратора

    public List<User> GetUsers()
    {
      List<User> users = new List<User>();
      MySqlCommand command = new MySqlCommand(getAllUsers, connection);
      MySqlDataReader reader = command.ExecuteReader();
      while (reader.Read())
      {
        string login = reader[1].ToString();
        string pass = reader[2].ToString();
        int userRole = Int32.Parse(reader[3].ToString());
        UserRole role = (UserRole)userRole;
        User user = new User(login, pass, "email", role);
        users.Add(user);
      }
      reader.Close();
      command.Dispose();
      return users;
    }

    #endregion


    #endregion
  }
}